資料庫延伸操作

第 11 屆 iThome 鐵人賽

DAY 5

自我挑戰組

MySQL 學習筆記系列第 5 篇

11th鐵人賽

stca

2019-09-21 23:48:45

2882 瀏覽

分享至

延伸

接續上回的班級資料庫，接下來試著模擬，如何在特定的條件下對資料庫做查詢

case..when運用，結尾點要加end(用在狀況比較多的情況下)

MariaDB [class]> select cname,
    -> case
    -> when csex ='f' then '小姐'
    -> when csex ='m' then '阿北'
    -> end                      '
    -> from bk1;                '
+--------+----------------------------------------------------------------+
| cname  | case
when csex ='f' then '小姐'
when csex ='m' then '阿北'
end |
+--------+----------------------------------------------------------------+
| 簡奉君 | 小姐                                                           |
| 黃靖輪 | 阿北                                                           |
| 潘四敬 | 阿北                                                           |
| 賴勝恩 | 阿北                                                           |
| 黎楚寧 | 小姐                                                           |
| 蔡中穎 | 阿北                                                           |
| 徐佳螢 | 小姐                                                           |
| 林雨媗 | 小姐                                                           |
| 林心儀 | 小姐                                                           |
| 王燕博 | 阿北                                                           |
| Brad   | 阿北                                                           |
+--------+----------------------------------------------------------------+
11 rows in set (0.000 sec)

類似js三元運算式的運用

//註:if(csex='f','小姐','阿北'):如果性別為女，則印出小姐，否則為阿北
MariaDB [class]> select cname,csex,if(csex='f','小姐','阿北') from bk1;
+--------+------+----------------------------+
| cname  | csex | if(csex='f','小姐','阿北') |
+--------+------+----------------------------+
| 簡奉君 | F    | 小姐                       |
| 黃靖輪 | M    | 阿北                       |
| 潘四敬 | M    | 阿北                       |
| 賴勝恩 | M    | 阿北                       |
| 黎楚寧 | F    | 小姐                       |
| 蔡中穎 | M    | 阿北                       |
| 徐佳螢 | F    | 小姐                       |
| 林雨媗 | F    | 小姐                       |
| 林心儀 | F    | 小姐                       |
| 王燕博 | M    | 阿北                       |
| Brad   | M    | 阿北                       |
+--------+------+----------------------------+
11 rows in set (0.049 sec)

case.when 運用(2)

中文成績90分以上要顯示A，80分以上要顯示B，70 分以上顯示C，60分以上顯示D，不及格則為E

MariaDB [class]> select cname, ch,
    -> case
    -> when ch >= 90 then 'A'
    -> when ch >= 80 then 'B'
    -> when ch >= 70 then 'C'
    -> when ch >= 60 then 'D'
    -> else 'E'
    -> end level//將上述when條件設成level欄位
    -> from bk1
    -> order by level,ch desc;//先按等級排列，等級相同再按成績排列
+--------+------+-------+
| cname  | ch   | level |
+--------+------+-------+
| 黃靖輪 |  100 | A     |
| Brad   |   99 | A     |
| 林雨媗 |   96 | A     |
| 林心儀 |   79 | C     |
| 蔡中穎 |   70 | C     |
| 賴勝恩 |   63 | D     |
| 徐佳螢 |   28 | E     |
| 王燕博 |   15 | E     |
| 簡奉君 |   13 | E     |
| 潘四敬 |    5 | E     |
| 黎楚寧 |    4 | E     |
+--------+------+-------+
11 rows in set (0.002 sec)

北風資料庫練習

接下來的是經典的北風資料庫，google 一下就知道它的資歷有多老了。雖然它的資料數不多，透過不同發問的句度，可以模擬真實情況中的查詢條件。
北風資料庫的內容是全英文，只要掌握名詞和動作指令的差別，應該不難上手。
下方列出一些常見的發問角度，如果能夠做到聽到問題馬上轉換成資料庫的語法，相信可以在實際狀況中得心應手。

秀出employees表格有幾組資料

MariaDB [northwind]> select  count(*)from employees;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.162 sec)

將員工的姓名列出來相同職稱擺在一起

MariaDB [northwind]> select title,firstname,lastname from employees
    ->  order by title;
+--------------------------+-----------+-----------+
| title                    | firstname | lastname  |
+--------------------------+-----------+-----------+
| Inside Sales Coordinator | Laura     | Callahan  |
| Sales Manager            | Steven    | Buchanan  |
| Sales Representative     | Nancy     | Davolio   |
| Sales Representative     | Janet     | Leverling |
| Sales Representative     | Margaret  | Peacock   |
| Sales Representative     | Michael   | Suyama    |
| Sales Representative     | Robert    | King      |
| Sales Representative     | Anne      | Dodsworth |
| Vice President, Sales    | Andrew    | Fuller    |
+--------------------------+-----------+-----------+
9 rows in set (0.065 sec)

職稱相同反向排序 lastname 排序


MariaDB [northwind]> select title,firstname,lastname from employees
    -> order by title desc ,lastname;
+--------------------------+-----------+-----------+
| title                    | firstname | lastname  |
+--------------------------+-----------+-----------+
| Vice President, Sales    | Andrew    | Fuller    |
| Sales Representative     | Nancy     | Davolio   |
| Sales Representative     | Anne      | Dodsworth |
| Sales Representative     | Robert    | King      |
| Sales Representative     | Janet     | Leverling |
| Sales Representative     | Margaret  | Peacock   |
| Sales Representative     | Michael   | Suyama    |
| Sales Manager            | Steven    | Buchanan  |
| Inside Sales Coordinator | Laura     | Callahan  |
+--------------------------+-----------+-----------+
9 rows in set (0.047 sec)

把Sale Representative剔除



MariaDB [northwind]> select title,firstname,lastname from employees
    -> where title <> 'Sales Representative';
+--------------------------+-----------+----------+
| title                    | firstname | lastname |
+--------------------------+-----------+----------+
| Vice President, Sales    | Andrew    | Fuller   |
| Sales Manager            | Steven    | Buchanan |
| Inside Sales Coordinator | Laura     | Callahan |
+--------------------------+-----------+----------+
3 rows in set (0.123 sec)

把region欄位為null的職員列表出來



MariaDB [northwind]> select firstname,lastname,region from employees
    -> where region is null;
+-----------+-----------+--------+
| firstname | lastname  | region |
+-----------+-----------+--------+
| Steven    | Buchanan  | NULL   |
| Michael   | Suyama    | NULL   |
| Robert    | King      | NULL   |
| Anne      | Dodsworth | NULL   |
+-----------+-----------+--------+
4 rows in set (0.066 sec)

region欄位有填寫的列表出來



MariaDB [northwind]> select firstname,lastname,region from employees
    -> where region is not null;
+-----------+-----------+--------+
| firstname | lastname  | region |
+-----------+-----------+--------+
| Nancy     | Davolio   | WA     |
| Andrew    | Fuller    | WA     |
| Janet     | Leverling | WA     |
| Margaret  | Peacock   | WA     |
| Laura     | Callahan  | WA     |
+-----------+-----------+--------+
5 rows in set (0.000 sec)

列出名字為N字母以後的列表

MariaDB [northwind]> select lastname from employees
    -> where lastname >=  'N' order by lastname;
+----------+
| lastname |
+----------+
| Peacock  |
| Suyama   |
+----------+
2 rows in set (0.047 sec)

庫存量少於安全庫存量的商品

MariaDB [northwind]> select productname,unitsinstock,reorderlevel from products
    -> where unitsinstock <= reorderlevel;
+---------------------------+--------------+--------------+
| productname               | unitsinstock | reorderlevel |
+---------------------------+--------------+--------------+
| Chang                     |           17 |           25 |
| Aniseed Syrup             |           13 |           25 |
| Chef Anton's Gumbo Mix    |            0 |            0 |
| Queso Cabrales            |           22 |           30 |
| Alice Mutton              |            0 |            0 |
| Sir Rodney's Scones       |            3 |            5 |
| Thuringer Rostbratwurst   |            0 |            0 |
| Nord-Ost Matjeshering     |           10 |           15 |
| Gorgonzola Telino         |            0 |           20 |
| Mascarpone Fabioli        |            9 |           25 |
| Gravad lax                |           11 |           25 |
| Ipoh Coffee               |           17 |           25 |
| Rogede sild               |            5 |           15 |
| Chocolade                 |           15 |           25 |
| Maxilaku                  |           10 |           15 |
| Perth Pasties             |            0 |            0 |
| Gnocchi di nonna Alice    |           21 |           30 |
| Wimmers gute Semmelknodel |           22 |           30 |
| Louisiana Hot Spiced Okra |            4 |           20 |
| Scottish Longbreads       |            6 |           15 |
| Outback Lager             |           15 |           30 |
| Longlife Tofu             |            4 |            5 |
+---------------------------+--------------+--------------+
22 rows in set (0.068 sec)

加一成運費後，訂單大於五百元的總運費訂單

MariaDB [northwind]> select orderid,freight,freight*1.1 as freighttotal
    -> from orders
    -> where freight >= 500;
+---------+-----------+--------------+
| orderid | freight   | freighttotal |
+---------+-----------+--------------+
|   10372 |  890.7800 |    979.85800 |
|   10479 |  708.9500 |    779.84500 |
|   10514 |  789.9500 |    868.94500 |
|   10540 | 1007.6400 |   1108.40400 |
|   10612 |  544.0800 |    598.48800 |
|   10691 |  810.0500 |    891.05500 |
|   10816 |  719.7800 |    791.75800 |
|   10897 |  603.5400 |    663.89400 |
|   10912 |  580.9100 |    639.00100 |
|   10983 |  657.5400 |    723.29400 |
|   11017 |  754.2600 |    829.68600 |
|   11030 |  830.7500 |    913.82500 |
|   11032 |  606.1900 |    666.80900 |
+---------+-----------+--------------+
13 rows in set (0.188 sec)

列出折扣的清單(取十筆資料)

MariaDB [northwind]> select discount from `order details` limit 10;
+----------+
| discount |
+----------+
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|     0.15 |
|     0.15 |
|     0.05 |
|     0.05 |
+----------+

列出單價、數量、折扣及總價列表(十筆為一個分頁)

MariaDB [northwind]> select unitprice , quantity , discount,unitprice*quantity*(1-discount) from `order details` limit 0,10;
+-----------+-----------+----------+----------+---------------------------------+
| unitprice | quantity | discount | unitprice*quantity*(1-discount) |
+-----------+----------+----------+---------------------------------+
|   14.0000 |       12 |        0 |                             168 |
|    9.8000 |       10 |        0 |                              98 |
|   34.8000 |        5 |        0 |                             174 |
|   18.6000 |        9 |        0 |                           167.4 |
|   42.4000 |       40 |        0 |                            1696 |
|    7.7000 |       10 |        0 |                              77 |
|   42.4000 |       35 |     0.15 |              1261.3999911546707 |
|   16.8000 |       15 |     0.15 |              214.19999849796295 |
|   16.8000 |        6 |     0.05 |               95.75999992489814 |
|   15.6000 |       15 |     0.05 |               222.2999998256564 |
+-----------+----------+----------+---------------------------------+
10 rows in set (0.080 sec)

統計目前為止商品編號三號的產品總銷售量

MariaDB [northwind]> select productid from products limit 3;
+-----------+
| productid |
+-----------+
|         1 |
|         2 |
|         3 |
+-----------+
3 rows in set (0.057 sec)


MariaDB [northwind]> select sum(quantity) total from `order details`
    
-> where productid=3;

+-------+
| total |
+-------+
|   328 |
+-------+
1 row in set (0.082 sec)

各個城市裡面，員工有幾個

MariaDB [northwind]> select city, count(*) as nums from employees
    
-> group by city;
+----------+------+
| city     | nums |
+----------+------+
| Kirkland |    1 |
| London   |    4 |
| Redmond  |    1 |
| Seattle  |    2 |
| Tacoma   |    1 |
+----------+------+
5 rows in set (0.079 sec)

有哪些城市是居住員工兩個(含)以上的?

MariaDB [northwind]> select city, count(*) as nums from employees
    
-> group by city
    
-> having count(*) >=2;
+---------+------+
| city    | nums |
+---------+------+
| London  |    4 |
| Seattle |    2 |
+---------+------+
2 rows in set (0.001 sec)

在我的員工中，職務為業務代表，且居住城市為兩個人以上,按序排列

MariaDB [northwind]> select city ,count(*) nums
    
-> from employees
    
-> where title like 'Sales Re%'
-> group by city
    
-> having nums >=2
    
-> order by nums;
+--------+------+
| city   | nums |
+--------+------+
| London |    3 |
+--------+------+
1 row in set (0.020 sec)

員工分別住在哪幾個城市(用group列出)

MariaDB [northwind]> select city from employees group by city;

+----------+
| city     |
+----------+
| Kirkland |
| London   |
| Redmond  |
| Seattle  |
| Tacoma   |
+----------+
5 rows in set (0.001 sec)

另一種方法(用distinct:不同的)

MariaDB [northwind]> select distinct city from employees;

+----------+
| city     |
+----------+
| Seattle  |
| Tacoma   |
| Kirkland |
| Redmond  |
| London   |
+----------+
5 rows in set (0.001 sec)

有幾個不同的城市?

MariaDB [northwind]> select count(distinct city) from employees;

+----------------------+
| count(distinct city) |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.001 sec)

訂單明細表裡面我要查看各個商品的總銷售量

MariaDB [northwind]> select productid, sum(quantity) total from `order details`
    
-> group by productid;

+-----------+-------+
| productid | total |
+-----------+-------+
|         1 |   828 |
|         2 |  1057 |
|         3 |   328 |
|         4 |   453 |
|         5 |   298 |
|         6 |   301 |
|         7 |   763 |
|....中間省略

|        73 |   293 |
|        74 |   297 |
|        75 |  1155 |
|        76 |   981 |
|        77 |   791 |
+-----------+-------+
77 rows in set (0.046 sec)

查看訂單明細表裡面查看商品賣出量少於兩百的

MariaDB [northwind]> select productid, sum(quantity) total from `order details`
    
-> group by productid
    
-> having  total< 200;

+-----------+-------+
| productid | total |
+-----------+-------+
|         9 |    95 |
|        15 |   122 |
|        37 |   125 |
|        48 |   138 |
|        67 |   184 |
+-----------+-------+
5 rows in set (0.110 sec)

商品實際單價跟銷售價格會不會有所不同 ??

MariaDB [northwind]> select unitprice from`order details`
    
-> where productid=9;

+-----------+
| unitprice |
+-----------+
|   77.6000 |
|   97.0000 |
|   97.0000 |
|   97.0000 |
|   97.0000 |
+-----------+
5 rows in set (0.001 sec)

各個商品銷售出去的實際平均單價(在歷史訂單中)

MariaDB [northwind]> select productid,avg(unitprice) avgPrice
    
-> from `order details`
    
-> group by productid;

+-----------+--------------+
| productid | avgPrice     |
+-----------+--------------+
|         1 |  17.14736842 |
|         2 |  17.87727273 |
|         3 |   9.50000000 |
....中間省略|
|        75 |   7.37934783 |
|        76 |  16.98461538 |
|        77 |  12.11052632 |
+-----------+--------------+
77 rows in set (0.003 sec)

哪些客戶訂單量超過15以上，並由多到少排序

MariaDB [northwind]> select customerid ,count(*) nums from orders
    
-> group by customerid
-> having nums >= 15
    
-> order by nums desc ;
+------------+------+
| customerid | nums |
+------------+------+
| SAVEA      |   31 |
| ERNSH      |   30 |
| QUICK      |   28 |
| FOLKO      |   19 |
| HUNGO      |   19 |
| RATTC      |   18 |
| HILAA      |   18 |
| BERGS      |   18 |
| BONAP      |   17 |
| WARTH      |   15 |
| FRANK      |   15 |
| LEHMS      |   15 |
+------------+------+
12 rows in set (0.103 sec)

在單價顯示中，前面加入錢$符號(concat()用法)

//註:concat()行為都當作字串方式處理

MariaDB [northwind]> select unitprice,concat('$',unitprice) from products;

+-----------+-----------------------+
| unitprice | concat('$',unitprice) |
+-----------+-----------------------+
|   18.0000 | $18.0000              |
|   19.0000 | $19.0000              |
|   10.0000 | $10.0000              |
|   22.0000 | $22.0000              |
|   21.3500 | $21.3500              |
|   25.0000 | $25.0000              |
|   30.0000 | $30.0000              |
|   40.0000 | $40.0000              |
|   97.0000 | $97.0000              |
|   31.0000 | $31.0000              |
|   21.0000 | $21.0000              |
|   38.0000 | $38.0000              |
|    6.0000 | $6.0000               |
|   23.2500 | $23.2500              |
|   15.5000 | $15.5000              |
|   17.4500 | $17.4500              |
|   39.0000 | $39.0000              |
|   62.5000 | $62.5000              |
|    9.2000 | $9.2000               |
|   81.0000 | $81.0000              |
|   10.0000 | $10.0000              |
|   21.0000 | $21.0000              |
|    9.0000 | $9.0000               |
|    4.5000 | $4.5000               |
|   14.0000 | $14.0000              |
|   31.2300 | $31.2300              |
|   43.9000 | $43.9000              |
|   45.6000 | $45.6000              |
|  123.7900 | $123.7900             |
|   25.8900 | $25.8900              |
|   12.5000 | $12.5000              |
|   32.0000 | $32.0000              |
|    2.5000 | $2.5000               |
|   14.0000 | $14.0000              |
|   18.0000 | $18.0000              |
|   19.0000 | $19.0000              |
|   26.0000 | $26.0000              |
|  263.5000 | $263.5000             |
|   18.0000 | $18.0000              |
|   18.4000 | $18.4000              |
|    9.6500 | $9.6500               |
|   14.0000 | $14.0000              |
|   46.0000 | $46.0000              |
|   19.4500 | $19.4500              |
|    9.5000 | $9.5000               |
|   12.0000 | $12.0000              |
|    9.5000 | $9.5000               |
|   12.7500 | $12.7500              |
|   20.0000 | $20.0000              |
|   16.2500 | $16.2500              |
|   53.0000 | $53.0000              |
|    7.0000 | $7.0000               |
|   32.8000 | $32.8000              |
|    7.4500 | $7.4500               |
|   24.0000 | $24.0000              |
|   38.0000 | $38.0000              |
|   19.5000 | $19.5000              |
|   13.2500 | $13.2500              |
|   55.0000 | $55.0000              |
|   34.0000 | $34.0000              |
|   28.5000 | $28.5000              |
|   49.3000 | $49.3000              |
|   43.9000 | $43.9000              |
|   33.2500 | $33.2500              |
|   21.0500 | $21.0500              |
|   17.0000 | $17.0000              |
|   14.0000 | $14.0000              |
|   12.5000 | $12.5000              |
|   36.0000 | $36.0000              |
|   15.0000 | $15.0000              |
|   21.5000 | $21.5000              |
|   34.8000 | $34.8000              |
|   15.0000 | $15.0000              |
|   10.0000 | $10.0000              |
|    7.7500 | $7.7500               |
|   18.0000 | $18.0000              |
|   13.0000 | $13.0000              |
+-----------+-----------------------+
77 rows in set (0.111 sec)

round 四捨五入練習

MariaDB [northwind]> select round(3.14,1);

+---------------+
| round(3.14,1) |
+---------------+
|           3.1 |
+---------------+
1 row in set (0.048 sec)

MariaDB [northwind]> select round(3.15,1);

+---------------+
| round(3.15,1) |
+---------------+
|           3.2 |
+---------------+
1 row in set (0.000 sec)

MariaDB [northwind]> select round(3.15,0);

+---------------+
| round(3.15,0) |
+---------------+
|             3 |
+---------------+
1 row in set (0.000 sec)

MariaDB [northwind]> select round(3.55,0);

+---------------+
| round(3.55,0) |
+---------------+
|             4 |
+---------------+
1 row in set (0.000 sec)


MariaDB [northwind]> select round(13.55,-1);

+-----------------+
| round(13.55,-1) |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.000 sec)

MariaDB [northwind]> select round(15.55,-1);

+-----------------+
| round(15.55,-1) |
+-----------------+
|              20 |
+-----------------+
1 row in set (0.000 sec)

計算員工到職的時候是幾歲-練習1(用年計算即可)

MariaDB [northwind]> select lastname,birthdate,hiredate,
    
-> year(hiredate)-year(birthdate) hireage
    
-> from employees;

+-----------+---------------------+---------------------+---------+
| lastname  | birthdate           | hiredate            | hireage |
+-----------+---------------------+---------------------+---------+
| Davolio   | 1948-12-08 00:00:00 | 1992-05-01 00:00:00 |      44 |
| Fuller    | 1952-02-19 00:00:00 | 1992-08-14 00:00:00 |      40 |
| Leverling | 1963-08-30 00:00:00 | 1992-04-01 00:00:00 |      29 |
| Peacock   | 1937-09-19 00:00:00 | 1993-05-03 00:00:00 |      56 |
| Buchanan  | 1955-03-04 00:00:00 | 1993-10-17 00:00:00 |      38 |
| Suyama    | 1963-07-02 00:00:00 | 1993-10-17 00:00:00 |      30 |
| King      | 1960-05-29 00:00:00 | 1994-01-02 00:00:00 |      34 |
| Callahan  | 1958-01-09 00:00:00 | 1994-03-05 00:00:00 |      36 |
| Dodsworth | 1966-01-27 00:00:00 | 1994-11-15 00:00:00 |      28 |
+-----------+---------------------+---------------------+---------+
9 rows in set (0.042 sec)

計算出自己年齡的天數(現在日期與出生日期的時間差)

//DATEDIFF() 函數返回兩個日期之間的天數。

MariaDB [northwind]> select datediff('2019-08-13','1984-04-06');
MariaDB [northwind]> select datediff('2019-08-13','1984-04-06');
+-------------------------------------+
| datediff('2019-08-13','1984-04-06') |
+-------------------------------------+
|                               12912 |
+-------------------------------------+
1 row in set (0.049 sec)

#計算員工到職的時候是幾歲-練習2(用datediff())

MariaDB [northwind]> select lastname,birthdate,hiredate,
    
-> floor(datediff(hiredate,birthdate)/365) hireage
    
-> from employees;

+-----------+---------------------+---------------------+---------+
| lastname  | birthdate           | hiredate            | hireage |
+-----------+---------------------+---------------------+---------+
| Davolio   | 1948-12-08 00:00:00 | 1992-05-01 00:00:00 |      43 |
| Fuller    | 1952-02-19 00:00:00 | 1992-08-14 00:00:00 |      40 |
| Leverling | 1963-08-30 00:00:00 | 1992-04-01 00:00:00 |      28 |
| Peacock   | 1937-09-19 00:00:00 | 1993-05-03 00:00:00 |      55 |
| Buchanan  | 1955-03-04 00:00:00 | 1993-10-17 00:00:00 |      38 |
| Suyama    | 1963-07-02 00:00:00 | 1993-10-17 00:00:00 |      30 |
| King      | 1960-05-29 00:00:00 | 1994-01-02 00:00:00 |      33 |
| Callahan  | 1958-01-09 00:00:00 | 1994-03-05 00:00:00 |      36 |
| Dodsworth | 1966-01-27 00:00:00 | 1994-11-15 00:00:00 |      28 |
+-----------+---------------------+---------------------+---------+
9 rows in set (0.048 sec)

請列出員工的生日月份(用month())

MariaDB [northwind]> select month(birthdate) from employees;

+------------------+
| month(birthdate) |
+------------------+
|               12 |
|                2 |
|                8 |
|                9 |
|                3 |
|                7 |
|                5 |
|                1 |
|                1 |
+------------------+
9 rows in set (0.048 sec)

利用date_format()

//函数用于以不同的格式显示日期/时间数据；%m是指月，数值(00-12)
//來源:https://www.w3school.com.cn/sql/func_date_format.asp

MariaDB [northwind]> select date_format(birthdate,'%m') from employees;

+-----------------------------+
| date_format(birthdate,'%m') |
+-----------------------------+
| 12                          |
| 02                          |
| 08                          |
| 09                          |
| 03                          |
| 07                          |
| 05                          |
| 01                          |
| 01                          |
+-----------------------------+
9 rows in set (0.046 sec)

MariaDB [northwind]> select date_format(birthdate,'%Y年%m月') from employees;

+-----------------------------------+
| date_format(birthdate,'%Y年%m月') |
+-----------------------------------+
| 1948年12月                        |
| 1952年02月                        |
| 1963年08月                        |
| 1937年09月                        |
| 1955年03月                        |
| 1963年07月                        |
| 1960年05月                        |
| 1958年01月                        |
| 1966年01月                        |
+-----------------------------------+
9 rows in set (0.047 sec)

利用extract():萃取資料

//ex:extract(year from '1999-07-02 01:02:03')，提取後值為1999

MariaDB [northwind]> select extract(month from birthdate) from employees;
+-------------------------------+
| extract(month from birthdate) |
+-------------------------------+
|                            12 |
|                             2 |
|                             8 |
|                             9 |
|                             3 |
|                             7 |
|                             5 |
|                             1 |
|                             1 |
+-------------------------------+
9 rows in set (0.041 sec)

找出訂單中orderid為10741 的客戶id代碼為何?

MariaDB [northwind]> select orderid, customerid from orders where orderid='10741';

MariaDB [northwind]> select orderid, customerid from orders where orderid='10741';
+---------+------------+
| orderid | customerid |
+---------+------------+
|   10741 | AROUT      |
+---------+------------+
1 row in set (0.001 sec)

承上，請找出客戶id為AROUT此公司的名稱為何?

MariaDB [northwind]> select companyname , customerid from customers
    
-> where customerid ='AROUT';

+-----------------+------------+
| companyname     | customerid |
+-----------------+------------+
| Around the Horn | AROUT      |
+-----------------+------------+
1 row in set (0.073 sec)

#將上面兩個指令用一句敘述句指令查公司名稱(子查詢練習)

//訂單10741的訂單是哪一家公司所發出來的?

MariaDB [northwind]> select companyname from customers
    
-> where customerid = (select customerid from orders where orderid=10741);
+-----------------+
| companyname     |
+-----------------+
| Around the Horn |
+-----------------+
1 row in set (0.054 sec)

1997年度有那些客戶的訂單?

MariaDB [northwind]> select customerid from orders
    
-> where year(orderdate) = 1997;
+------------+
| customerid |
+------------+
| EASTC      |
| RATTC      |
| ERNSH      |
| ERNSH      |
| MAGAA      |
| LINOD      |
| QUEEN      |
| VICTE      |
| FRANS      |
+------------+
408 rows in set (0.001 sec)

承上，1997年度有那些客戶的訂單，並且將id群組化

MariaDB [northwind]> select customerid from orders
    
-> where year(orderdate) = 1997
    
-> group by customerid;
+------------+
| customerid |
+------------+
| ALFKI      |
| ANATR      |
| ANTON      |
| WANDK      |
| WARTH      |
| WELLI      |
| WHITC      |
| WILMK      |
| WOLZA      |
+------------+
86 rows in set (0.001 sec)

1997年下訂單的客戶公司為何?(取兩個客戶來查詢)

MariaDB [northwind]> select companyname from customers
    
-> where customerid = 'WOLZA' or customerid = 'WILMK';

//OR運算符用法，只要其中一個條件成立即可顯示此成立的紀錄結果

+----------------+
| companyname    |
+----------------+
| Wilman Kala    |
| Wolski  Zajazd |
+----------------+
2 rows in set (0.001 sec)

MariaDB [northwind]> select companyname from customers
    
-> where customerid in ('WOLZA','WILMK');

//IN 操作符允许我们在 WHERE 子句中规定多个值。
+----------------+
| companyname    |
+----------------+
| Wilman Kala    |
| Wolski  Zajazd |
+----------------+
2 rows in set (0.000 sec)

用子查詢方式，請查出1997年下訂單的客戶有哪幾家公司?(可參照上面兩個指令)

MariaDB [northwind]> select companyname from customers
    
-> where customerid in
    
-> (select customerid from orders
    
-> where year(orderdate) = 1997
    
-> group  by customerid);
+------------------------------------+
| companyname                        |
+------------------------------------+
| Alfreds Futterkiste                |
| Ana Trujillo Emparedados y helados |
| Antonio Moreno Taqueria            |
| Tradicao Hipermercados             |
| Trail's Head Gourmet Provisioners  |
| Vaffeljernet                       |
| Victuailles en stock               |
| Vins et alcools Chevalier          |
| Die Wandernde Kuh                  |
| Wartian Herkku                     |
| Wellington Importadora             |
| White Clover Markets               |
| Wilman Kala                        |
| Wolski  Zajazd                     |
+------------------------------------+
86 rows in set (0.061 sec)

10人小班的基本資料庫操作

運用 where... in...和 join

系列文

MySQL 學習筆記共 8 篇

RSS系列文訂閱系列文

4 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1123 組

團體組數

52 組

累計文章數

23096 篇

完賽人數

656 人

15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js

IT邦幫忙

MySQL 學習筆記系列 第 5 篇